-- 权限管理相关数据表
-- 数据库：refunddb
-- 服务器：10.1.200.169,1434
-- 账号：sa 密码：6hospital+

USE [refunddb]
GO

-- 1. 角色表
CREATE TABLE [dbo].[sys_roles](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[input_date] [datetime] NOT NULL DEFAULT (getdate()),
	[role_name] [nvarchar](50) NOT NULL,
	[role_code] [nvarchar](50) NOT NULL,
	[description] [nvarchar](200) NULL,
	[is_active] [bit] NOT NULL DEFAULT (1),
	[sort_order] [int] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_sys_roles] PRIMARY KEY CLUSTERED ([id] ASC),
	CONSTRAINT [UK_sys_roles_role_code] UNIQUE NONCLUSTERED ([role_code] ASC)
) ON [PRIMARY]
GO

-- 2. 菜单表
CREATE TABLE [dbo].[sys_menus](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[input_date] [datetime] NOT NULL DEFAULT (getdate()),
	[menu_name] [nvarchar](50) NOT NULL,
	[menu_code] [nvarchar](50) NOT NULL,
	[parent_id] [int] NULL,
	[menu_type] [nvarchar](10) NOT NULL DEFAULT ('menu'),
	[path] [nvarchar](200) NULL,
	[component] [nvarchar](200) NULL,
	[icon] [nvarchar](50) NULL,
	[sort_order] [int] NOT NULL DEFAULT (0),
	[is_active] [bit] NOT NULL DEFAULT (1),
	[is_visible] [bit] NOT NULL DEFAULT (1),
	[permission] [nvarchar](100) NULL,
	CONSTRAINT [PK_sys_menus] PRIMARY KEY CLUSTERED ([id] ASC),
	CONSTRAINT [UK_sys_menus_menu_code] UNIQUE NONCLUSTERED ([menu_code] ASC)
) ON [PRIMARY]
GO

-- 3. 角色菜单关联表
CREATE TABLE [dbo].[sys_role_menus](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[input_date] [datetime] NOT NULL DEFAULT (getdate()),
	[role_id] [int] NOT NULL,
	[menu_id] [int] NOT NULL,
	CONSTRAINT [PK_sys_role_menus] PRIMARY KEY CLUSTERED ([id] ASC),
	CONSTRAINT [UK_sys_role_menus] UNIQUE NONCLUSTERED ([role_id] ASC, [menu_id] ASC)
) ON [PRIMARY]
GO

-- 4. 用户角色关联表
CREATE TABLE [dbo].[sys_user_roles](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[input_date] [datetime] NOT NULL DEFAULT (getdate()),
	[user_id] [int] NOT NULL,
	[role_id] [int] NOT NULL,
	CONSTRAINT [PK_sys_user_roles] PRIMARY KEY CLUSTERED ([id] ASC),
	CONSTRAINT [UK_sys_user_roles] UNIQUE NONCLUSTERED ([user_id] ASC, [role_id] ASC)
) ON [PRIMARY]
GO

-- 添加外键约束
ALTER TABLE [dbo].[sys_role_menus] ADD CONSTRAINT [FK_sys_role_menus_role_id] 
FOREIGN KEY([role_id]) REFERENCES [dbo].[sys_roles] ([id]) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[sys_role_menus] ADD CONSTRAINT [FK_sys_role_menus_menu_id] 
FOREIGN KEY([menu_id]) REFERENCES [dbo].[sys_menus] ([id]) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[sys_user_roles] ADD CONSTRAINT [FK_sys_user_roles_user_id] 
FOREIGN KEY([user_id]) REFERENCES [dbo].[users] ([id]) ON DELETE CASCADE
GO

ALTER TABLE [dbo].[sys_user_roles] ADD CONSTRAINT [FK_sys_user_roles_role_id] 
FOREIGN KEY([role_id]) REFERENCES [dbo].[sys_roles] ([id]) ON DELETE CASCADE
GO

-- 添加字段注释
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_roles', @level2type=N'COLUMN',@level2name=N'id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据录入时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_roles', @level2type=N'COLUMN',@level2name=N'input_date'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'角色名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_roles', @level2type=N'COLUMN',@level2name=N'role_name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'角色编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_roles', @level2type=N'COLUMN',@level2name=N'role_code'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'角色描述' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_roles', @level2type=N'COLUMN',@level2name=N'description'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否启用' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_roles', @level2type=N'COLUMN',@level2name=N'is_active'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'排序号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_roles', @level2type=N'COLUMN',@level2name=N'sort_order'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_menus', @level2type=N'COLUMN',@level2name=N'id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据录入时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_menus', @level2type=N'COLUMN',@level2name=N'input_date'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'菜单名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_menus', @level2type=N'COLUMN',@level2name=N'menu_name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'菜单编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_menus', @level2type=N'COLUMN',@level2name=N'menu_code'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'父级菜单ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_menus', @level2type=N'COLUMN',@level2name=N'parent_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'菜单类型：menu-菜单，button-按钮' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_menus', @level2type=N'COLUMN',@level2name=N'menu_type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'路由路径' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_menus', @level2type=N'COLUMN',@level2name=N'path'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'组件路径' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_menus', @level2type=N'COLUMN',@level2name=N'component'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'菜单图标' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_menus', @level2type=N'COLUMN',@level2name=N'icon'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'排序号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_menus', @level2type=N'COLUMN',@level2name=N'sort_order'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否启用' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_menus', @level2type=N'COLUMN',@level2name=N'is_active'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否可见' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_menus', @level2type=N'COLUMN',@level2name=N'is_visible'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'权限标识' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'sys_menus', @level2type=N'COLUMN',@level2name=N'permission'
GO

-- 插入初始数据

-- 插入默认角色
INSERT INTO [dbo].[sys_roles] ([role_name], [role_code], [description], [sort_order]) VALUES
('超级管理员', 'admin', '系统超级管理员，拥有所有权限', 1),
('审核员', 'auditor', '退款申请审核员，负责审核退款申请', 2),
('查询员', 'viewer', '数据查询员，只能查看和统计数据', 3)
GO

-- 插入菜单数据
INSERT INTO [dbo].[sys_menus] ([menu_name], [menu_code], [parent_id], [menu_type], [path], [component], [icon], [sort_order], [permission]) VALUES
-- 一级菜单
('退款审核管理', 'refund_audit', NULL, 'menu', '/refund', 'Layout', 'el-icon-document', 1, 'refund:view'),
('汇总统计', 'statistics', NULL, 'menu', '/statistics', 'Layout', 'el-icon-data-analysis', 2, 'statistics:view'),
('字典设置', 'dictionary', NULL, 'menu', '/dictionary', 'Layout', 'el-icon-setting', 3, 'dict:view'),
('权限管理', 'permission', NULL, 'menu', '/permission', 'Layout', 'el-icon-user', 4, 'permission:view'),
('系统管理', 'system', NULL, 'menu', '/system', 'Layout', 'el-icon-tools', 5, 'system:view'),

-- 退款审核管理子菜单
('退款申请列表', 'refund_list', 1, 'menu', '/refund/list', 'refund/RefundList', '', 1, 'refund:list'),
('审核处理', 'refund_audit_detail', 1, 'button', '', '', '', 2, 'refund:audit'),
('查看详情', 'refund_detail', 1, 'button', '', '', '', 3, 'refund:detail'),

-- 汇总统计子菜单
('统计报表', 'statistics_report', 2, 'menu', '/statistics/report', 'statistics/StatisticsReport', '', 1, 'statistics:report'),
('数据导出', 'statistics_export', 2, 'button', '', '', '', 2, 'statistics:export'),

-- 字典设置子菜单
('字典管理', 'dict_manage', 3, 'menu', '/dictionary/manage', 'dictionary/DictManage', '', 1, 'dict:manage'),
('字典编辑', 'dict_edit', 3, 'button', '', '', '', 2, 'dict:edit'),

-- 权限管理子菜单
('用户管理', 'user_manage', 4, 'menu', '/permission/users', 'permission/UserManage', '', 1, 'user:manage'),
('角色管理', 'role_manage', 4, 'menu', '/permission/roles', 'permission/RoleManage', '', 2, 'role:manage'),
('菜单管理', 'menu_manage', 4, 'menu', '/permission/menus', 'permission/MenuManage', '', 3, 'menu:manage'),

-- 系统管理子菜单
('系统设置', 'system_config', 5, 'menu', '/system/config', 'system/SystemConfig', '', 1, 'system:config'),
('操作日志', 'system_log', 5, 'menu', '/system/log', 'system/SystemLog', '', 2, 'system:log')
GO

-- 为超级管理员角色分配所有菜单权限
INSERT INTO [dbo].[sys_role_menus] ([role_id], [menu_id])
SELECT 1, id FROM [dbo].[sys_menus]
GO

-- 为审核员角色分配退款审核和统计相关权限
INSERT INTO [dbo].[sys_role_menus] ([role_id], [menu_id])
SELECT 2, id FROM [dbo].[sys_menus] WHERE menu_code IN (
    'refund_audit', 'refund_list', 'refund_audit_detail', 'refund_detail',
    'statistics', 'statistics_report', 'statistics_export'
)
GO

-- 为查询员角色分配查看权限
INSERT INTO [dbo].[sys_role_menus] ([role_id], [menu_id])
SELECT 3, id FROM [dbo].[sys_menus] WHERE menu_code IN (
    'refund_audit', 'refund_list', 'refund_detail',
    'statistics', 'statistics_report'
)
GO

-- 为现有admin用户分配超级管理员角色
INSERT INTO [dbo].[sys_user_roles] ([user_id], [role_id])
SELECT u.id, 1 FROM [dbo].[users] u WHERE u.username = 'admin'
GO

PRINT '权限管理表创建完成！'
PRINT '已创建以下表：'
PRINT '1. sys_roles - 角色表'
PRINT '2. sys_menus - 菜单表'
PRINT '3. sys_role_menus - 角色菜单关联表'
PRINT '4. sys_user_roles - 用户角色关联表'
PRINT ''
PRINT '已插入初始数据：'
PRINT '- 3个默认角色：超级管理员、审核员、查询员'
PRINT '- 完整的菜单结构'
PRINT '- 角色权限分配'
PRINT '- admin用户已分配超级管理员角色'